<?php
class Application_Model_General
{
	private $db, $m_cache;
	private static $_instance = null;

	public function __construct()
	{
		$this->db = Zend_Registry::get('db');
		$this->m_cache = Zend_Registry::get('cache');
	}

	public static function getInstance()
	{
		if (null === self::$_instance) self::$_instance = new self();
		return self::$_instance;
	}

	public function getSource()
	{
		return array(
			'jsk' => 'Jobseekers',
			'emp' => 'Employers',
			'adm' => 'Admin'
		);
	}

	/**
	 * @author	Ryan <nguyenhoaivien@yahoo.com>
	 * @date	Oc 16, 2013
	 * @param	language
	 * @return	array
	 * @desc	return list industries
	 */
	public function getIndustries($id=NULL)
	{
		if($id==NULL) return $this->db->select('*')->from('industries')->order(array('industry_priority'))->query()->fetchAll();
		return $this->db->select('*')->from('industries')->where('industry_id = ?', $id)->query()->fetch();
	}

	public function saveIndustry($arrData)
	{
		$id = $arrData['industry_id'];
		unset($arrData['industry_id']);
		if(empty($id))
		{
			$arrData['industry_updatedate'] = date('Y-m-d H:i:s');
			return $this->db->insert('industries', $arrData);
		}
		return $this->db->update('industries', $arrData, 'industry_id = '.(int)$id);
	}

	public function deleteIndustry($ids)
	{
		return $this->db->delete('industries', 'industry_id in ('.implode(',', (array)$ids).')');
	}

	public function publishIndustry($ids, $status)
	{
		return $this->db->update('industries',
				array('industry_status'=>$status), 
				'industry_id in ('.implode(',', (array)$ids).')'
		);
	}

	/**
	 * @author  Ryan <nguyenhoaivien@yahoo.com>
	 * @date	Oc 16, 2013
	 * @param string strLang, int type, int country_id
	 * @return array
	 * @desc return list locations
	 */
	public function getLocations($id=NULL)
	{
		if($id==NULL) return $this->db->select('*')->from('locations')->order(array('location_priority'))->query()->fetchAll();
		return $this->db->select('*')->from('locations')->where('location_id = ?', $id)->query()->fetch();
	}

	public function saveLocation($arrData)
	{
		$id = $arrData['location_id'];
		unset($arrData['location_id']);
		if(empty($id))
		{
			$arrData['modified'] = time();
			return $this->db->insert('locations', $arrData);
		}
		return $this->db->update('locations', $arrData, 'location_id = '.(int)$id);
	}
	
	/**
	 * @author  Ryan <nguyenhoaivien@yahoo.com>
	 * @date	Oc 16, 2013
	 * @param int id, string strLang
	 * @return array or string
	 * @desc: return list country
	 */
	public function getCountry($id=NULL, $strLang=LANGUAGE)
	{
		try
		{
			static $arrResults;
			if(!isset($arrResults))
			{
				$strCache = 'Model_Common_getCountry_'.$strLang;
				if(($arrResults = $this->m_cache->load($strCache))===FALSE)
				{
					$stmt = $this->db->prepare('CALL fe_common_lst_countries(:p_varLang);');
					$stmt -> bindParam('p_varLang', $strLang, PDO::PARAM_STR);
					$stmt -> execute();
					$arrResults  = $stmt->fetchAll();
					$stmt->closeCursor();
					$arrTemp = array();
					foreach($arrResults as $R)
						$arrTemp[$R['COUNTRY_ID']] = $R['COUNTRY_NAME'];
					$arrResults = $arrTemp;
					$this->m_cache->save($arrResults, $strCache,  array(MEMCACHE_PREF), DEFAULT_CACHE_DATE);
				}
			}
			if($id===NULL) return $arrResults;
			return isset($arrResults[$id]) ? $arrResults[$id] : NULL;
		}
		catch (Exception $e)
		{
			throw new Exception($e);
		}
	}

	/**
	 * @author  Ryan <nguyenhoaivien@yahoo.com>
	 * @date	Oc 16, 2013
	 * @param int id, string strLang
	 * @return array or string
	 * @desc: return list job level name
	 */
	public function getJobLevels($id=NULL)
	{
		try
		{
			if($id==NULL) return $this->db->select('*')->from('levels')->order(array('level_priority'))->query()->fetchAll();
			return $this->db->select('*')->from('levels')->where('LEVEL_ID = ?', $id)->query()->fetch();
		}
		catch (Exception $e)
		{
			throw new Exception($e);
		}
	}

	public function saveLevel($arrData)
	{
		$id = $arrData['level_id'];
		unset($arrData['level_id']);
		if(empty($id))
		{
			$arrData['level_updatedate'] = date('Y-m-d H:i:s');
			return $this->db->insert('levels', $arrData);
		}
		return $this->db->update('levels', $arrData, 'level_id = '.(int)$id);
	}

	public function deleteLevel($ids)
	{
		return $this->db->delete('levels', 'level_id in ('.implode(',', (array)$ids).')');
	}

	public function publishLevel($ids, $status)
	{
		return $this->db->update('levels',
				array('level_status'=>$status), 
				'level_id in ('.implode(',', (array)$ids).')'
		);
	}

	/**
	 * @author  Ryan <nguyenhoaivien@yahoo.com>
	 * @date	Oc 18, 2013
	 * @param int id, string strLang
	 * @return array or string
	 * @desc: return list job degrees
	 */
	public function getJobDegrees($id=NULL)
	{
		try
		{
			if($id==NULL) return $this->db->select('*')->from('degrees')->order(array('degree_priority'))->query()->fetchAll();
			return $this->db->select('*')->from('degrees')->where('DEGREE_ID = ?', $id)->query()->fetch();
		}
		catch (Exception $e)
		{
			throw new Exception($e);
		}
	}

	public function saveDegree($arrData)
	{
		$id = $arrData['degree_id'];
		unset($arrData['degree_id']);
		if(empty($id))
		{
			$arrData['degree_updatedate'] = date('Y-m-d H:i:s');
			return $this->db->insert('degrees', $arrData);
		}
		return $this->db->update('degrees', $arrData, 'degree_id = '.(int)$id);
	}

	public function deleteDegree($ids)
	{
		return $this->db->delete('degrees',  'degree_id in ('.implode(',', (array)$ids).')');
	}

	public function publishDegree($ids, $status)
	{
		return $this->db->update('degrees',
				array('degree_status'=>$status), 
				'degree_id in ('.implode(',', (array)$ids).')'
		);
	}

	/**
	 * @author  Ryan <nguyenhoaivien@yahoo.com>
	 * @date	Nov 11, 2013
	 * @param int id, string strLang
	 * @return array or string
	 * @desc: return list auto response
	 */
	public function searchAutoResponse($params, &$total)
	{
		$query = $this->db->select();
		if(!empty($params['object'])) $query->where('object', $params['object']);
		if(!empty($params['keywords'])) $query->where('title_'.LANGUAGE.' like ?', '%'.$params['keywords'].'%');
		$query->from('autoresponses', array('count(id) as items'));
		$row = $query->query()->fetch();
		if(!$total = $row['ITEMS']) return NULL;
		$limit = empty($params['limit']) ? 20 : (int)$params['limit'];
		$page = empty($params['page']) ? 1 : (int)$params['page'];
		return $query	-> reset(Zend_Db_Select::COLUMNS)
						-> columns(array('id', 'title_'.LANGUAGE.' as title', 'modified', 'by_user'))
						-> order('modified', 'desc')
						-> limit($limit, max(0, $page-1)*$limit)
						-> query()->fetchAll();
	}

	public function getAutoResponse($id)
	{
		return $this->db->select()->from('autoresponses')->where('id=?', (int)$id)->query()->fetch();
	}

	public function saveAutoResponse($arrData)
	{
		$arrData['modified'] = time();
		if(empty($arrData['id']))
		{
			unset($arrData['id']);
			$arrData['created'] = time();
			$this->db->insert('autoresponses', $arrData);
			return $this->db->lastInsertId();
		}
		$this->db->update('autorespones', $arrData, 'id='.$arrData['id']);
		return $arrData['id'];
	}

	public function deleteAutoResponse($ids)
	{
		return $this->db->delete('autoresponses',  'degree_id in ('.implode(',', (array)$ids).')');
	}
}